import { sqlUtil } from '../common/db.js';

const NoticeboardModel = {
    // Get post list
    async getList(query, page) {
        let sql = `
            SELECT p.*, t.Name as TagName, s.Name as AuthorName, s.Photo as AuthorAvatar,
                   CASE WHEN l.StudentID IS NOT NULL THEN 1 ELSE 0 END as IsLiked
            FROM noticeboardPosts p
            LEFT JOIN tags t ON p.TagID = t.TagID
            LEFT JOIN students s ON p.StudentID = s.StudentID
            LEFT JOIN likes l ON p.PostID = l.PostID 
                AND l.StudentID = ? AND l.IsDeleted = 0
            WHERE p.IsDeleted = 0
        `;
        let params = [query.studentId];

        // Add keyword search
        if (query.keyword) {
            sql += ` AND (
                LOWER(p.Title) LIKE ? OR 
                LOWER(p.Content) LIKE ?
            )`;
            const keyword = `%${query.keyword.toLowerCase()}%`;
            params.push(keyword, keyword);
        }

        // Filter by tag
        if (query.tagId) {
            sql += ` AND p.TagID = ?`;
            params.push(query.tagId);
        }

        // Filter by type (popular/my posts)
        if (query.filter === 'popular') {
            sql += ` AND p.IsHot = 1`;
        } else if (query.filter === 'mypost') {
            sql += ` AND p.StudentID = ?`;
            params.push(query.studentId);
        }

        sql += ` ORDER BY p.CreateTime DESC`;
        return sqlUtil.getLimit(sql, params, page);
    },

    // Get post details
    async getById(postId, studentId) {
        const sql = `
            SELECT p.*, t.Name as TagName, s.Name as AuthorName, s.Photo as AuthorAvatar,
                   CASE WHEN l.StudentID IS NOT NULL THEN 1 ELSE 0 END as IsLiked
            FROM noticeboardPosts p
            LEFT JOIN tags t ON p.TagID = t.TagID
            LEFT JOIN students s ON p.StudentID = s.StudentID
            LEFT JOIN likes l ON p.PostID = l.PostID 
                AND l.StudentID = ? AND l.IsDeleted = 0
            WHERE p.PostID = ? AND p.IsDeleted = 0
        `;
        return sqlUtil.get(sql, [studentId, postId]);
    },

    // Create new post
    async create(post) {
        const sql = `
            INSERT INTO noticeboardPosts (Title, Content, StudentID, TagID, Media)
            VALUES (?, ?, ?, ?, ?)
        `;
        return sqlUtil.run(sql, [
            post.title,
            post.content,
            post.studentId,
            post.tagId,
            post.media
        ]);
    },

    // Update post
    async update(postId, post) {
        const sql = `
            UPDATE noticeboardPosts 
            SET Title = ?, Content = ?, TagID = ?, Media = ?, UpdateTime = CURRENT_TIMESTAMP
            WHERE PostID = ? AND IsDeleted = 0
        `;
        return sqlUtil.run(sql, [
            post.title,
            post.content,
            post.tagId,
            post.media,
            postId
        ]);
    },

    // Delete post (soft delete)
    async delete(postId) {
        const sql = `UPDATE noticeboardPosts SET IsDeleted = 1 WHERE PostID = ?`;
        return sqlUtil.run(sql, [postId]);
    },

    // Toggle like/unlike on post
    async toggleLike(postId, studentId) {
        // Start transaction
        return await sqlUtil.withTransaction(async () => {
            // Check if already liked
            const existingSql = `
                SELECT IsDeleted FROM likes 
                WHERE PostID = ? AND StudentID = ?
            `;
            const existing = await sqlUtil.get(existingSql, [postId, studentId]);
            if (!existing) {
                // Add new like
                const insertSql = `
                    INSERT INTO likes (PostID, StudentID)
                    VALUES (?, ?)
                `;
                await sqlUtil.run(insertSql, [postId, studentId]);
                await this.updateLikeCount(postId, 1);
                // Check if post should be marked as hot
                await this.checkAndSetHotPost(postId);
                return true;
            } else {
                // Update like status
                const newStatus = existing.IsDeleted ? 0 : 1;
                const updateSql = `
                    UPDATE likes 
                    SET IsDeleted = ?
                    WHERE PostID = ? AND StudentID = ?
                `;
                await sqlUtil.run(updateSql, [newStatus, postId, studentId]);
                await this.updateLikeCount(postId, newStatus ? -1 : 1);
                // Check if post should be marked as hot
                await this.checkAndSetHotPost(postId);
                return !newStatus;
            }
        });
    },

    // Update like count
    async updateLikeCount(postId, increment) {
        const sql = `
            UPDATE noticeboardPosts 
            SET LikeCount = LikeCount + ?
            WHERE PostID = ?
        `;
        return sqlUtil.run(sql, [increment, postId]);
    },

    // Get post comments
    async getComments(postId) {
        const sql = `
            SELECT c.*, s.Name as AuthorName, s.Photo as AuthorAvatar
            FROM comments c
            LEFT JOIN students s ON c.StudentID = s.StudentID
            WHERE c.PostID = ? AND c.IsDeleted = 0
            ORDER BY c.CreateTime DESC
        `;
        return sqlUtil.all(sql, [postId]);
    },

    // Add comment to post
    async addComment(postId, content, studentId) {
        return await sqlUtil.withTransaction(async () => {
            const sql = `
                INSERT INTO comments (PostID, StudentID, Content)
                VALUES (?, ?, ?)
            `;
            const result = await sqlUtil.run(sql, [postId, studentId, content]);
            
            // Update post comment count
            await this.updateCommentCount(postId);

            // Update post update time
            await this.updatePostUpdateTime(postId);
            
            // Check if post should be marked as hot
            await this.checkAndSetHotPost(postId);
            
            return result;
        });
    },

    // Update post update time
    async updatePostUpdateTime(postId) {
        const sql = `UPDATE noticeboardPosts SET UpdateTime = CURRENT_TIMESTAMP WHERE PostID = ?`;
        return sqlUtil.run(sql, [postId]);
    },

    // Delete comment
    async deleteComment(commentId, studentId) {
        return await sqlUtil.withTransaction(async () => {
            const sql = `
                UPDATE comments 
                SET IsDeleted = 1 
                WHERE CommentID = ? AND StudentID = ?
            `;
            const result = await sqlUtil.run(sql, [commentId, studentId]);
            
            // Update post comment count
            const post = await this.getCommentPost(commentId);
            if (post) {
                await this.updateCommentCount(post.PostID);
                // Check if post should be marked as hot
                await this.checkAndSetHotPost(post.PostID);
            }
            
            return result;
        });
    },

    // Get post that a comment belongs to
    async getCommentPost(commentId) {
        const sql = `
            SELECT PostID 
            FROM comments 
            WHERE CommentID = ?
        `;
        return sqlUtil.get(sql, [commentId]);
    },

    // Update post comment count
    async updateCommentCount(postId) {
        const sql = `
            UPDATE noticeboardPosts
            SET CommentCount = (
                SELECT COUNT(*) 
                FROM comments 
                WHERE PostID = ? AND IsDeleted = 0
            )
            WHERE PostID = ?
        `;
        return sqlUtil.run(sql, [postId, postId]);
    },

    // Set post as hot
    async setHotPost(postId) {
        const sql = `UPDATE noticeboardPosts SET IsHot = 1 WHERE PostID = ?`;
        return sqlUtil.run(sql, [postId]);
    },

    // Check and set post as hot if it meets criteria
    // Post becomes hot when it has 10+ likes/comments or is set by admin
    async checkAndSetHotPost(postId) {
        const sql = `
            SELECT 
                p.LikeCount,
                p.CommentCount,
                p.IsHot
            FROM noticeboardPosts p
            WHERE p.PostID = ? AND p.IsDeleted = 0
        `;
        
        const post = await sqlUtil.get(sql, [postId]);
        if (!post) return;

        // If already hot, no need to check
        if (post.IsHot) return;

        // Check criteria: 10+ likes or 10+ comments
        if (post.LikeCount >= 10 || post.CommentCount >= 10) {
            await this.setHotPost(postId);
        }
    }
};

export default NoticeboardModel;
